package kitaplaner.database;

import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author kristina
 */


public class Database {
    private String DB_HOST;
    private String DB_PORT;
    private String DB_LOGIN;
    private String DB_PW;
    private String DB_NAME;
    private String DB_URL;
    private String DB_TREIBER;
    
    public Database()
    {
        Credentials creds = new Credentials();
        DB_HOST = creds.getDB_HOST();
        DB_PORT = creds.getDB_PORT();
        DB_LOGIN = creds.getDB_LOGIN();
        DB_PW = creds.getDB_PW();
        DB_NAME = creds.getDB_NAME();
        DB_URL = "jdbc:mysql://"+DB_HOST+":"+DB_PORT+"/"+DB_NAME;
        DB_TREIBER = "com.mysql.jdbc.Driver";
    }

    private Connection conn;
    private Statement statement;
    private ResultSet result;


    public Kita initKita (Connection conn)
    {
        this.conn = conn;
        Kita kita = new Kita();
        
        kita.setGrp(this.getGruppen(conn));
        kita.setKind(this.getKinder(conn));
        kita.setMa(this.getMitarbeiter(conn));
        kita.setPerioden(this.getPerioden(conn));
        kita.setSchichten(this.getSchichten(conn));
        kita.setStatus(this.getStatus(conn));
        kita.setUrlaub(this.getUrlaubstage(conn));
        kita.setSchichtEintrag(this.getSchichtEintrag(conn));
        kita.setFehltage(this.getFehltage(conn));
        kita.setArbStd(this.getTagesStd(conn));
                
        return kita;
    }

    public Connection Connect2DB()
    {
        Connection conn = null;

        try {
	    System.out.println("* Treiber laden");
      	    Class.forName(DB_TREIBER).newInstance();
        }
        catch (Exception e) {
            System.err.println("Unable to load driver.");
            e.printStackTrace();
        }
        try {
	    System.out.println("* Verbindung aufbauen");
	    conn = DriverManager.getConnection(DB_URL, DB_LOGIN, DB_PW);

        }
        catch (SQLException sqle) {
            System.out.println("SQLException: " + sqle.getMessage());
            System.out.println("SQLState: " + sqle.getSQLState());
            System.out.println("VendorError: " + sqle.getErrorCode());
            sqle.printStackTrace();
        }

        return conn;
    }

    public Connection checkConnection(Connection conn)
    {
        try {
            if (conn.isClosed()) {
                conn = this.Connect2DB();
            }
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }

        return conn;
    }

    public boolean closeDB (Connection conn)
    {
        boolean closed = false;

        try
        {
            conn.close();
            closed = true;
	    System.out.println("* Datenbank-Verbindung beenden");
        }
        catch (SQLException sqle) {
            System.out.println("SQLException: " + sqle.getMessage());
            System.out.println("SQLState: " + sqle.getSQLState());
            System.out.println("VendorError: " + sqle.getErrorCode());
            sqle.printStackTrace();
        }

        return closed;
    }
    
    public ArrayList<Mitarbeiter> getMitarbeiter (Connection conn)
    {
        ArrayList<Mitarbeiter> mitarbeiter = new ArrayList<Mitarbeiter>();

        if (conn != null)
        {
            try
            {
                statement = conn.createStatement();
                result = statement.executeQuery("SELECT * FROM mitarbeiter WHERE flagdelete NOT LIKE 'X'");

                while (result.next())
                {
                    Mitarbeiter ma = new Mitarbeiter();

                    ma.setEmail(result.getString("Email"));

                    ma.setGebDatum(result.getDate("GebDat"));

                    ma.setGrpID(result.getInt("GruppenId"));
                    
                    ma.setHausnummer(result.getString("Hausnummer"));

                    ma.setId(result.getInt("ID"));

                    ma.setNachname(result.getString("Nachname"));

                    ma.setVorname(result.getString("Vorname"));

                    ma.setPlz(result.getInt("PLZ"));

                    ma.setStadt(result.getString("Stadt"));

                    ma.setStatusID(result.getInt("StatusID"));

                    ma.setStdMABesprechung(result.getDouble("StdMaBesprechung"));

                    ma.setStdVorbereitung(result.getDouble("StdVorbereitungszeit"));

                    ma.setStdWocheSoll(result.getDouble("StdWocheSoll"));

                    ma.setStraße(result.getString("Straße"));

                    ma.setTelefon(result.getString("Telefon"));

                    ma.setUrlaubstageSoll(result.getDouble("UrlaubSoll"));

                    mitarbeiter.add(ma);
                }
                result.close();
                statement.close();
            }
            catch (SQLException sqle)
            {
                System.out.println("SQLException: " + sqle.getMessage());
                System.out.println("SQLState: " + sqle.getSQLState());
                System.out.println("VendorError: " + sqle.getErrorCode());
                sqle.printStackTrace();
            }
            catch (NullPointerException npe)
            {
                npe.printStackTrace();
            }
        }
        return mitarbeiter;
    }

    public ArrayList<Kind> getKinder (Connection conn)
    {
        ArrayList<Kind> kinder = new ArrayList<Kind>();

        if (conn != null)
        {
            try
            {
                statement = conn.createStatement();
                result = statement.executeQuery("SELECT * FROM kinder WHERE flagdelete NOT LIKE 'X'");

                while (result.next())
                {
                    Kind kind = new Kind();

                    kind.setId(Integer.parseInt(result.getString("ID")));
                    kind.setEmail(result.getString("Email"));
                    kind.setHausnr(result.getString("Hausnummer"));
                    kind.setNachname(result.getString("Nachname"));
                    kind.setVorname(result.getString("Vorname"));
                    kind.setStadt(result.getString("Stadt"));
                    kind.setStraße(result.getString("Straße"));
                    kind.setTelefon(result.getString("Telefon"));
                    kind.setAllergie(result.getString("Allergien"));

                    kind.setGebDat(result.getDate("Geburtsdatum"));

                    kind.setGrpID(result.getInt("GruppenId"));
                    kind.setId(result.getInt("ID"));
                    kind.setPlz(result.getInt("PLZ"));

                    kind.setSöz_start(result.getTime("SÖZ_Start"));
                    kind.setSöz_end(result.getTime("SÖZ_End"));

                    kinder.add(kind);
                }
                result.close();
                statement.close();
            }
            catch (SQLException sqle)
            {
                System.out.println("SQLException: " + sqle.getMessage());
                System.out.println("SQLState: " + sqle.getSQLState());
                System.out.println("VendorError: " + sqle.getErrorCode());
                sqle.printStackTrace();
            }
            catch (NullPointerException npe)
            {
                npe.printStackTrace();
            }
        }
        return kinder;
    }

    public ArrayList<Gruppe> getGruppen (Connection conn)
    {
        ArrayList gruppen = new ArrayList();

        if (conn != null)
        {
            try
            {
                statement = conn.createStatement();
                result = statement.executeQuery("SELECT * FROM gruppe WHERE flagdelete NOT LIKE 'X'");

                while (result.next())
                {
                    Gruppe grp = new Gruppe();

                    grp.setId(result.getInt("ID"));

                    grp.setName(result.getString("Name"));

                    grp.setStart(result.getTime("StartZeit"));

                    grp.setEnd(result.getTime("EndZeit"));

                    grp.setVorbZeit(result.getDouble("VorbereitungszeitGes"));

                    grp.setLeitZeit(result.getDouble("StdLeitungZeitaufwand"));

                    gruppen.add(grp);
                }
                result.close();
                statement.close();
            }
            catch (SQLException sqle)
            {
                System.out.println("SQLException: " + sqle.getMessage());
                System.out.println("SQLState: " + sqle.getSQLState());
                System.out.println("VendorError: " + sqle.getErrorCode());
                sqle.printStackTrace();
            }
            catch (NullPointerException npe)
            {
                npe.printStackTrace();
            }
        }

        return gruppen;
    }

    public ArrayList<Periode> getPerioden (Connection conn)
    {
        ArrayList<Periode> perioden = new ArrayList<Periode>();

        if (conn != null)
        {
            try
            {
                statement = conn.createStatement();
                result = statement.executeQuery("SELECT * FROM perioden");

                while (result.next())
                {
                    Periode per = new Periode();

                    per.setId(result.getInt("ID"));

                    per.setMaId(result.getInt("MitarbeiterID"));

                    per.setStart(result.getTime("StartZeit"));

                    per.setEnd(result.getTime("EndZeit"));

                    perioden.add(per);
                }
                result.close();
                statement.close();
            }
            catch (SQLException sqle)
            {
                System.out.println("SQLException: " + sqle.getMessage());
                System.out.println("SQLState: " + sqle.getSQLState());
                System.out.println("VendorError: " + sqle.getErrorCode());
                sqle.printStackTrace();
            }
            catch (NullPointerException npe)
            {
                npe.printStackTrace();
            }
        }
        return perioden;
    }

    public ArrayList<Schicht> getSchichten (Connection conn)
    {
        ArrayList<Schicht> schichten = new ArrayList<Schicht>();

        if (conn != null)
        {
            try
            {
                statement = conn.createStatement();
                result = statement.executeQuery("SELECT * FROM schicht");

                while (result.next())
                {
                    Schicht schicht = new Schicht();

                    schicht.setId(result.getInt("ID"));

                    schicht.setGrpId(result.getInt("GruppenID"));

                    schicht.setStart(result.getTime("StartZeit"));

                    schicht.setEnd(result.getTime("EndZeit"));

                    schicht.setMaMax(result.getInt("MitarbeiterAnzahlMax"));

                    schicht.setMaMin(result.getInt("MitarbeiterAnzahlMin"));

                    schichten.add(schicht);
                }
                result.close();
                statement.close();
            }
            catch (SQLException sqle)
            {
                System.out.println("SQLException: " + sqle.getMessage());
                System.out.println("SQLState: " + sqle.getSQLState());
                System.out.println("VendorError: " + sqle.getErrorCode());
                sqle.printStackTrace();
            }
            catch (NullPointerException npe)
            {
                npe.printStackTrace();
            }
        }
        return schichten;
    }
    
    public ArrayList<SchichtEintrag> getSchichtEintrag (Connection conn)
    {
        ArrayList<SchichtEintrag> schichtEintragListe = new ArrayList<SchichtEintrag>();

        if (conn != null)
        {
            try
            {
                statement = conn.createStatement();
                result = statement.executeQuery("SELECT * FROM schichteintrag");

                while (result.next())
                {
                    SchichtEintrag schichtEintrag = new SchichtEintrag();
                    
                    schichtEintrag.setId(result.getInt("ID"));
                    schichtEintrag.setGruppenID(result.getInt("GruppenID"));
                    schichtEintrag.setMitarbeiterID(result.getInt("MitarbeiterID"));
                    schichtEintrag.setStartZeit(result.getTime("StartZeit"));
                    schichtEintrag.setEndZeit(result.getTime("EndZeit"));
                    schichtEintrag.setPauseStart(result.getTime("PauseStart"));
                    schichtEintrag.setPauseEnde(result.getTime("PauseEnde"));
                    schichtEintrag.setVbStart(result.getTime("vbStart"));
                    schichtEintrag.setVbEnde(result.getTime("vbEnde"));
                    schichtEintrag.setDatum(result.getDate("Datum"));

                    schichtEintragListe.add(schichtEintrag);
                }
                result.close();
                statement.close();
            }
            catch (SQLException sqle)
            {
                System.out.println("SQLException: " + sqle.getMessage());
                System.out.println("SQLState: " + sqle.getSQLState());
                System.out.println("VendorError: " + sqle.getErrorCode());
                sqle.printStackTrace();
            }
            catch (NullPointerException npe)
            {
                npe.printStackTrace();
            }
        }
        return schichtEintragListe;
    }
    

    public ArrayList<Status> getStatus (Connection conn)
    {
        ArrayList<Status> stas = new ArrayList<Status>();

        if (conn != null)
        {
            try
            {
                statement = conn.createStatement();
                result = statement.executeQuery("SELECT * FROM status");

                while (result.next())
                {
                    Status status = new Status();

                    status.setId(result.getInt("ID"));

                    status.setName(result.getString("Name"));

                    stas.add(status);
                }
                result.close();
                statement.close();
            }
            catch (SQLException sqle)
            {
                System.out.println("SQLException: " + sqle.getMessage());
                System.out.println("SQLState: " + sqle.getSQLState());
                System.out.println("VendorError: " + sqle.getErrorCode());
                sqle.printStackTrace();
            }
            catch (NullPointerException npe)
            {
                npe.printStackTrace();
            }
        }
        return stas;
    }

    public ArrayList<Urlaubstage> getUrlaubstage (Connection conn)
    {
        ArrayList<Urlaubstage> urlaub = new ArrayList<Urlaubstage>();

        if (conn != null)
        {
            try
            {
                statement = conn.createStatement();
                result = statement.executeQuery("SELECT * FROM wunschurlaubstag");

                while (result.next())
                {
                    Urlaubstage tag = new Urlaubstage();

                    tag.setId(result.getInt("ID"));

                    tag.setMaId(result.getInt("MitarbeiterID"));

                    tag.setDatum(result.getDate("Datum"));

                    urlaub.add(tag);
                }
                result.close();
                statement.close();
            }
            catch (SQLException sqle)
            {
                System.out.println("SQLException: " + sqle.getMessage());
                System.out.println("SQLState: " + sqle.getSQLState());
                System.out.println("VendorError: " + sqle.getErrorCode());
                sqle.printStackTrace();
            }
            catch (NullPointerException npe)
            {
                npe.printStackTrace();
            }
        }
        return urlaub;
    }

    public ArrayList<Fehltag> getFehltage(Connection conn) {
        ArrayList<Fehltag> fehltage = new ArrayList<Fehltag>();

        if (conn != null)
        {
            try
            {
                statement = conn.createStatement();
                result = statement.executeQuery("SELECT * FROM fehltage");

                while (result.next())
                {
                    Fehltag fehltag = new Fehltag();

                    fehltag.setId(result.getInt("ID"));

                    fehltag.setMaId(result.getInt("MitarbeiterID"));

                    fehltag.setDatum(result.getDate("Datum"));
                    
                    fehltag.setFehlgrund(result.getString("Grund"));

                    fehltage.add(fehltag);
                }
                result.close();
                statement.close();
            }
            catch (SQLException sqle)
            {
                System.out.println("SQLException: " + sqle.getMessage());
                System.out.println("SQLState: " + sqle.getSQLState());
                System.out.println("VendorError: " + sqle.getErrorCode());
                sqle.printStackTrace();
            }
            catch (NullPointerException npe)
            {
                npe.printStackTrace();
            }
        }
        return fehltage;
    }

    public ArrayList<TagesStd> getTagesStd(Connection conn) {
        ArrayList<TagesStd> tage = new ArrayList<TagesStd>();

        if (conn != null)
        {
            try
            {
                statement = conn.createStatement();
                result = statement.executeQuery("SELECT * FROM tagesstd");

                while (result.next())
                {
                    TagesStd tag = new TagesStd();

                    tag.setId(result.getInt("ID"));

                    tag.setMaId(result.getInt("maID"));

                    tag.setDate(result.getDate("datum"));

                    tag.setStdIst(result.getDouble("stdIst"));

                    tag.setWeek(result.getInt("week"));

                    tage.add(tag);
                }
                result.close();
                statement.close();
            }
            catch (SQLException sqle)
            {
                System.out.println("SQLException: " + sqle.getMessage());
                System.out.println("SQLState: " + sqle.getSQLState());
                System.out.println("VendorError: " + sqle.getErrorCode());
                sqle.printStackTrace();
            }
            catch (NullPointerException npe)
            {
                npe.printStackTrace();
            }
        }
        return tage;
    }

    /*********************************CHANGE***********************************/
    public Boolean changeGrp (Connection conn, Gruppe grp)
    {
        Boolean success = false;
        try {
            statement = conn.createStatement();
            statement.executeUpdate("UPDATE gruppe SET Name='"+grp.getName()
                                   +"', StartZeit='"+grp.getStart()+"', "
                                   +"EndZeit='"+grp.getEnd()+"', "
                                   +"VorbereitungszeitGes='"+grp.getVorbZeit()
                                   +"', StdLeitungZeitaufwand='"
                                   +grp.getLeitZeit()+"'"
                                   +" WHERE ID = '"+grp.getId()+"';");
            statement.close();
            success = true;
        }
        catch (SQLException sqle)
        {
             System.out.println("SQLException: " + sqle.getMessage());
             System.out.println("SQLState: " + sqle.getSQLState());
             System.out.println("VendorError: " + sqle.getErrorCode());
             sqle.printStackTrace();
        }
        return success;
    }

    public Boolean changeMA (Connection conn, Mitarbeiter ma)
    {
        Boolean success = false;
        try {
            statement = conn.createStatement();
            statement.executeUpdate("UPDATE mitarbeiter SET Vorname='"
                                   + ma.getVorname()+"', Nachname='"
                                   + ma.getNachname()+"', Straße='"
                                   + ma.getStraße()+"', Hausnummer='"
                                   + ma.getHausnummer()+"', PLZ='"+ ma.getPlz()
                                   + "', Stadt='"+ma.getStadt()+"', Telefon='"
                                   + ma.getTelefon()+"', Email='"+ ma.getEmail()
                                   + "', StdWocheSoll='"+ma.getStdWocheSoll()
                                   + "', UrlaubSoll='"+ma.getUrlaubstageSoll()
                                   + "', GruppenId='"+ma.getGrpID()
                                   + "', StatusID='"+ma.getStatusID()
                                   + "', StdVorbereitungszeit='"
                                   + ma.getStdVorbereitung()
                                   + "', StdMABesprechung='"
                                   + ma.getStdMABesprechung()
                                   + "', GebDat='"+ma.getGebDatum()
                                   +"' WHERE ID = '"+ma.getId()+"';");
            statement.close();
            success = true;
        }
        catch (SQLException sqle)
        {
             System.out.println("SQLException: " + sqle.getMessage());
             System.out.println("SQLState: " + sqle.getSQLState());
             System.out.println("VendorError: " + sqle.getErrorCode());
             sqle.printStackTrace();
        }
        return success;
    }

    public Boolean changeKind (Connection conn, Kind kind)
    {
        Boolean success = false;
        try {
            statement = conn.createStatement();
            statement.executeUpdate("UPDATE kinder SET Vorname='"
                                   + kind.getVorname()+"', Nachname='"
                                   + kind.getNachname()+"', Straße='"
                                   + kind.getStraße()+"', Hausnummer='"
                                   + kind.getHausnr()+"', PLZ='"
                                   + kind.getPlz()+ "', Stadt='"
                                   +kind.getStadt()+"', Telefon='"
                                   + kind.getTelefon()+"', Email='"
                                   + kind.getEmail()+ "', GruppenId='"
                                   +kind.getGrpID()+ "', Allergien='"
                                   +kind.getAllergie()+ "', SÖZ_Start='"
                                   +kind.getSöz_start()+ "', SÖZ_End='"
                                   +kind.getSöz_end()+ "', Geburtsdatum='"
                                   +kind.getGebDat()
                                   +"' WHERE ID = '"+kind.getId()+"';");
            statement.close();
            success = true;
        }
        catch (SQLException sqle)
        {
             System.out.println("SQLException: " + sqle.getMessage());
             System.out.println("SQLState: " + sqle.getSQLState());
             System.out.println("VendorError: " + sqle.getErrorCode());
             sqle.printStackTrace();
        }
        return success;
    }

    public Boolean changeArbeitsStd(Connection conn, TagesStd tag)
    {
        Boolean success = false;
        try {
            statement = conn.createStatement();
            statement.executeUpdate("UPDATE tagesstd SET stdIst='"
                        +tag.getStdIst()+"' WHERE ID='"+tag.getId()+"';");

            statement.close();
            success = true;
        } catch (SQLException sqle) {
             System.out.println("SQLException: " + sqle.getMessage());
             System.out.println("SQLState: " + sqle.getSQLState());
             System.out.println("VendorError: " + sqle.getErrorCode());
             sqle.printStackTrace();
        }
        return success;
    }
    /*********************************CHANGE***********************************/

    /*********************************DELETE***********************************/
    public Boolean deleteKind (Connection conn, int kindID)
    {
        Boolean success = false;
        try {
            statement = conn.createStatement();
            statement.executeUpdate("UPDATE kinder SET flagdelete='X' "+
                    "WHERE ID = '"+kindID+"';");
            statement.close();
            success = true;
        }
        catch (SQLException sqle)
        {
             System.out.println("SQLException: " + sqle.getMessage());
             System.out.println("SQLState: " + sqle.getSQLState());
             System.out.println("VendorError: " + sqle.getErrorCode());
             sqle.printStackTrace();
        }
        return success;
    }

    public Boolean deleteMA (Connection conn, int maID)
    {
        Boolean success = false;
        try {
            statement = conn.createStatement();
            statement.executeUpdate("UPDATE mitarbeiter SET flagdelete='X' "
                    + "WHERE ID = '"+maID+"';");
            statement.close();
            success = true;
        }
        catch (SQLException sqle)
        {
             System.out.println("SQLException: " + sqle.getMessage());
             System.out.println("SQLState: " + sqle.getSQLState());
             System.out.println("VendorError: " + sqle.getErrorCode());
             sqle.printStackTrace();
        }
        return success;
    }
    
    public Boolean deleteGrp (Connection conn, int grpID)
    {
        Boolean success = false;
        try {
            statement = conn.createStatement();
            statement.executeUpdate("UPDATE gruppe SET flagdelete='X'"
                    + " WHERE ID = '"+grpID+"';");
            deleteSchichten(conn, grpID);
            statement.close();
            success = true;
        }
        catch (SQLException sqle)
        {
             System.out.println("SQLException: " + sqle.getMessage());
             System.out.println("SQLState: " + sqle.getSQLState());
             System.out.println("VendorError: " + sqle.getErrorCode());
             sqle.printStackTrace();
        }
        return success;
    }

    public Boolean deleteSchichten (Connection conn, int grpID)
    {
        Boolean success = false;
        try {
            statement = conn.createStatement();
            statement.executeUpdate("DELETE FROM schicht WHERE GruppenID = '"
                    +grpID+"';");
            statement.close();
            success = true;
        }
        catch (SQLException sqle)
        {
             System.out.println("SQLException: " + sqle.getMessage());
             System.out.println("SQLState: " + sqle.getSQLState());
             System.out.println("VendorError: " + sqle.getErrorCode());
             sqle.printStackTrace();
        }
        return success;
    }

    public Boolean deleteSchicht (Connection conn, int schichtID)
    {
        Boolean success = false;
        try {
            statement = conn.createStatement();
            statement.executeUpdate("DELETE FROM schicht WHERE ID = '"+schichtID
                    +"';");
            statement.close();
            success = true;
        } 
        catch (SQLException sqle)
        {
             System.out.println("SQLException: " + sqle.getMessage());
             System.out.println("SQLState: " + sqle.getSQLState());
             System.out.println("VendorError: " + sqle.getErrorCode());
             sqle.printStackTrace();
        }
        return success;
    }
    
    public Boolean deleteStatus (Connection conn, int statusID)
    {
        Boolean success = false;
        try {
            statement = conn.createStatement();
            statement.executeUpdate("DELETE FROM status WHERE ID = '"+statusID
                    +"';");
            statement.close();
            success = true;
        }
        catch (SQLException sqle)
        {
             System.out.println("SQLException: " + sqle.getMessage());
             System.out.println("SQLState: " + sqle.getSQLState());
             System.out.println("VendorError: " + sqle.getErrorCode());
             sqle.printStackTrace();
        }
        return success;
    }
    
    public Boolean deletePeriode (Connection conn, int perID)
    {
        Boolean success = false;
        try {
            statement = conn.createStatement();
            statement.executeUpdate("DELETE FROM perioden WHERE ID = '"+perID
                    +"';");
            statement.close();
            success = true;
        } catch (SQLException sqle) {
             System.out.println("SQLException: " + sqle.getMessage());
             System.out.println("SQLState: " + sqle.getSQLState());
             System.out.println("VendorError: " + sqle.getErrorCode());
             sqle.printStackTrace();
        }
        return success;
    }
    
    public Boolean deleteUrlaubstag (Connection conn, int urlaubsID)
    {
        Boolean success = false;
        try {
            statement = conn.createStatement();
            statement.executeUpdate("DELETE FROM wunschurlaubstag WHERE ID = '"+urlaubsID
                    +"';");
            statement.close();
            success = true;
        } catch (SQLException sqle) {
             System.out.println("SQLException: " + sqle.getMessage());
             System.out.println("SQLState: " + sqle.getSQLState());
             System.out.println("VendorError: " + sqle.getErrorCode());
             sqle.printStackTrace();
        }
        return success;
    }
    
    public Boolean deleteFehltag (Connection conn, int fehltagID)
    {
        Boolean success = false;
        try {
            statement = conn.createStatement();
            statement.executeUpdate("DELETE FROM fehltage WHERE ID = '"+fehltagID
                    +"';");
            statement.close();
            success = true;
        } catch (SQLException sqle) {
             System.out.println("SQLException: " + sqle.getMessage());
             System.out.println("SQLState: " + sqle.getSQLState());
             System.out.println("VendorError: " + sqle.getErrorCode());
             sqle.printStackTrace();
        }
        return success;
    }
    
    /*********************************DELETE***********************************/

    /*********************************INSERT***********************************/
    public Boolean insertKind (Connection conn, Kind kind)
    {
        Boolean success = false;
        try {
            statement = conn.createStatement();
            statement.executeUpdate("INSERT INTO kinder (Vorname, Nachname,"
                    + "Straße, Hausnummer, PLZ, Stadt, Geburtsdatum, Telefon, "
                    + "Email, GruppenId, Allergien, SÖZ_Start, SÖZ_End) "
                    + "VALUES ('"
                    + kind.getVorname()+"','"+ kind.getNachname() +"','"
                    + kind.getStraße() +"','"+ kind.getHausnr() +"','"
                    + kind.getPlz() +"','"+ kind.getStadt() +"','"
                    + kind.getGebDat()+"','"+ kind.getTelefon() + "','"
                    + kind.getEmail() +"','"+ kind.getGrpID() +"','"
                    + kind.getAllergie() +"','"+ kind.getSöz_start() + "','"
                    + kind.getSöz_end() +"');");
            statement.close();
            success = true;
        } catch (SQLException sqle) {
             System.out.println("SQLException: " + sqle.getMessage());
             System.out.println("SQLState: " + sqle.getSQLState());
             System.out.println("VendorError: " + sqle.getErrorCode());
             sqle.printStackTrace();
        }
        return success;
    }

    public Boolean insertGruppe (Connection conn, Gruppe grp)
    {
        Boolean success = false;
        try {
            statement = conn.createStatement();
            statement.executeUpdate("INSERT INTO gruppe (Name, StartZeit,"
                    + "EndZeit, VorbereitungszeitGes, StdLeitungZeitaufwand) "
                    + "VALUES ('"
                    + grp.getName()+"','"+ grp.getStart() +"','"
                    + grp.getEnd() +"','"+ grp.getVorbZeit() +"','"
                    + grp.getLeitZeit() +"');");
            statement.close();
            success = true;
        } catch (SQLException sqle) {
             System.out.println("SQLException: " + sqle.getMessage());
             System.out.println("SQLState: " + sqle.getSQLState());
             System.out.println("VendorError: " + sqle.getErrorCode());
             sqle.printStackTrace();
        }
        return success;
    }

    public Boolean insertMa (Connection conn, Mitarbeiter ma)
    {
        Boolean success = false;
        try {
            statement = conn.createStatement();
            statement.executeUpdate("INSERT INTO mitarbeiter (Vorname, Nachname,"
                    + "Straße, Hausnummer, PLZ, Stadt, Telefon, Email, "
                    + "StdWocheSoll, UrlaubSoll, "
                    + "GruppenId, StatusID, StdVorbereitungszeit, "
                    + "StdMaBesprechung, GebDat) VALUES ('"
                    + ma.getVorname()+"','"+ ma.getNachname() +"','"
                    + ma.getStraße() +"','"+ ma.getHausnummer() +"','"
                    + ma.getPlz() +"','"+ ma.getStadt() +"','"+ ma.getTelefon()
                    + "','"+ ma.getEmail() +"','" + ma.getStdWocheSoll()
                    + "','"+ ma.getUrlaubstageSoll()+"','"+ma.getGrpID() +"','"
                    + ma.getStatusID()+"','"+ ma.getStdVorbereitung() +"','"
                    + ma.getStdMABesprechung() +"','"+ ma.getGebDatum() +"');");
            statement.close();
            success = true;
        } catch (SQLException sqle) {
             System.out.println("SQLException: " + sqle.getMessage());
             System.out.println("SQLState: " + sqle.getSQLState());
             System.out.println("VendorError: " + sqle.getErrorCode());
             sqle.printStackTrace();
        }
        return success;
    }

    public Boolean insertPeriod (Connection conn, Periode per)
    {
        Boolean success = false;
        try {
            statement = conn.createStatement();
            statement.executeUpdate("INSERT INTO perioden (MitarbeiterID, "
                    + "StartZeit, EndZeit) VALUES ('"
                    +per.getMaId()+"','"+ per.getStart() +"','"+
                    per.getEnd()+"');");
            statement.close();
            success = true;
        } catch (SQLException sqle) {
             System.out.println("SQLException: " + sqle.getMessage());
             System.out.println("SQLState: " + sqle.getSQLState());
             System.out.println("VendorError: " + sqle.getErrorCode());
             sqle.printStackTrace();
        }
        return success;
    }

    public Boolean insertStatus (Connection conn, Status stat)
    {
        Boolean success = false;
        try {
            statement = conn.createStatement();
            statement.executeUpdate("INSERT INTO status (Name) VALUES ('"+stat.getName()+"');");
            statement.close();
            success = true;
        } catch (SQLException sqle) {
             System.out.println("SQLException: " + sqle.getMessage());
             System.out.println("SQLState: " + sqle.getSQLState());
             System.out.println("VendorError: " + sqle.getErrorCode());
             sqle.printStackTrace();
        }
        return success;
    }

    public Boolean insertSchicht (Connection conn, Schicht schicht)
    {
        Boolean success = false;
        try {
            statement = conn.createStatement();
            statement.executeUpdate("INSERT INTO schicht (GruppenID, "
                    + "StartZeit, EndZeit, MitarbeiterAnzahlMAX, "
                    + "MitarbeiterAnzahlMIN) VALUES ('"+schicht.getGrpId()+"','"
                    + schicht.getStart() + "','" + schicht.getEnd()+"','"
                    + schicht.getMaMax() + "','" + schicht.getMaMin()+"');");
            statement.close();
            success = true;
        } catch (SQLException sqle) {
             System.out.println("SQLException: " + sqle.getMessage());
             System.out.println("SQLState: " + sqle.getSQLState());
             System.out.println("VendorError: " + sqle.getErrorCode());
             sqle.printStackTrace();
        }
        return success;
    }
    
    public Boolean insertSchichtEintrag (Connection conn, SchichtEintrag neuerEintrag)
    {
        Boolean success = false;
        try {
            statement = conn.createStatement();
            statement.executeUpdate("INSERT INTO schichtEintrag("
                    + "Datum, MitarbeiterID, GruppenID, "
                    + "StartZeit, EndZeit, PauseStart, PauseEnde, vbStart, vbEnde"
                    + ") VALUES ('"
                    + neuerEintrag.getDatum() + "','"
                    + neuerEintrag.getMitarbeiterID() + "','"
                    + neuerEintrag.getGruppenID()   + "','"
                    + neuerEintrag.getStartZeit() + "','"
                    + neuerEintrag.getEndZeit() + "','"
                    + neuerEintrag.getPauseStart() + "','"
                    + neuerEintrag.getPauseEnde() + "','"
                    + neuerEintrag.getVbStart() + "','"
                    + neuerEintrag.getVbEnde() + "');");

            statement.close();
            success = true;
        } catch (SQLException sqle) {
             System.out.println("SQLException: " + sqle.getMessage());
             System.out.println("SQLState: " + sqle.getSQLState());
             System.out.println("VendorError: " + sqle.getErrorCode());
             sqle.printStackTrace();
        }
        return success;
    }

    public Boolean insertUrlaubsTag(Connection conn, Urlaubstage neuerUrlaubstag) {
        Boolean success = false;
        try {
            statement = conn.createStatement();
            statement.executeUpdate("INSERT INTO wunschurlaubstag("
                    + "MitarbeiterID, Datum"
                    + ") VALUES ('"
                    + neuerUrlaubstag.getMaId() + "','"
                    + neuerUrlaubstag.getDatum() + "');");
            success = true;
            statement.close();
        } catch (SQLException sqle) {
             System.out.println("SQLException: " + sqle.getMessage());
             System.out.println("SQLState: " + sqle.getSQLState());
             System.out.println("VendorError: " + sqle.getErrorCode());
             sqle.printStackTrace();
        }
        return success;
    }

    public Boolean insertFehltag(Connection conn, Fehltag fehltag) {
        Boolean success = false;
        try {
            statement = conn.createStatement();
            statement.executeUpdate("INSERT INTO fehltage("
                    + "MitarbeiterID, Datum, Grund"
                    + ") VALUES ('"
                    + fehltag.getMaId() + "','"
                    + fehltag.getDatum() + "','"
                    + fehltag.getFehlgrund() + "');");

            statement.close();
            success = true;
        } catch (SQLException sqle) {
             System.out.println("SQLException: " + sqle.getMessage());
             System.out.println("SQLState: " + sqle.getSQLState());
             System.out.println("VendorError: " + sqle.getErrorCode());
             sqle.printStackTrace();
        }
        return success;
    }

    public Boolean insertArbeitsStd(Connection conn, TagesStd tag)
    {
        Boolean success = false;
        try {
            statement = conn.createStatement();
            statement.executeUpdate("INSERT INTO tagesstd("
                    + "maID, datum, stdIst, week"
                    + ") VALUES ('"
                    + tag.getMaId() + "','"
                    + tag.getDate() + "','"
                    + tag.getStdIst() + "','"
                    + tag.getWeek() + "');");

            statement.close();
            success = true;
        } catch (SQLException sqle) {
             System.out.println("SQLException: " + sqle.getMessage());
             System.out.println("SQLState: " + sqle.getSQLState());
             System.out.println("VendorError: " + sqle.getErrorCode());
             sqle.printStackTrace();
        }
        return success;
    }

    public Boolean insertErrorLog(Connection conn, String message)
    {
        Boolean success = false;
        try {
            statement = conn.createStatement();
            statement.executeUpdate("INSERT INTO fehler_log(message) VALUES ('"
                    + message + "');");
            statement.close();
            success = true;
        } catch (SQLException sqle) {
             System.out.println("SQLException: " + sqle.getMessage());
             System.out.println("SQLState: " + sqle.getSQLState());
             System.out.println("VendorError: " + sqle.getErrorCode());
             sqle.printStackTrace();
        }
        return success;
    }
    /*********************************INSERT***********************************/
}
